-- SQL SERVER 2000 TABLE CREATION SCRIPT FOR VIRTUAL FOOD DRIVE FLEX APP
-- VERSION 1.3: 13 AUG 2007
-- SCHEMA DESIGN BY: 360 FLEX SEATTLE CHARITY CODEJAM VOLUNTEERS (FURTHER CREDITS TO BE LISTED)
-- CODE BY: MIKE BROPHY (MIKE@RIACTANT.COM) ET AL


DROP TABLE dbo.USERS, dbo.ROLES, dbo.ADDRESS, dbo.COMMENTS, dbo.FOODDRIVES, dbo.ORGANIZATIONS, dbo.DONATIONS, dbo.PAGE_CONFIG, dbo.APP_CONFIG, dbo.USER_CAT, dbo.ORG_CAT, dbo.MATCHING_CAMPAIGNS

CREATE TABLE dbo.USERS
(	ID INT IDENTITY(1,1),
	EMAIL VARCHAR(255) NOT NULL, 
	PASSWORD VARCHAR(255) NOT NULL,
    ADDRESS_ID INT,
    ROLE_ID INT,
    FNAME VARCHAR(255) NOT NULL,
    LNAME VARCHAR(255) NOT NULL,
    CAT_ID INT,
    ACTIVE BIT,
    IS_DELETED BIT
)
GO

CREATE TABLE dbo.ROLES
    (   ID INT IDENTITY(1,1),
        ROLE_NAME VARCHAR(255) NOT NULL
    )
GO

CREATE TABLE dbo.ADDRESS
    (
        ID INT IDENTITY(1,1),
        ADDRESS1 VARCHAR(255) NOT NULL,
        ADDRESS2 VARCHAR(255),
        CITY VARCHAR(255) NOT NULL,
        STATE_PROVINCE VARCHAR(255) NOT NULL,
        POSTAL_CODE VARCHAR(255) NOT NULL,
        COUNTRY VARCHAR(255) NOT NULL,
        PHONE VARCHAR(255),
        FAX VARCHAR(255),
        ADDRESS_CAT_ID INT,
        USER_ORG_ID INT
    )
GO


CREATE TABLE dbo.COMMENTS
    (
        ID INT IDENTITY(1,1),
        USER_ID INT NOT NULL,
        FOODDRIVE_ID INT NOT NULL,
        COMMENT_TEXT TEXT NOT NULL,
        VISIBLE BIT NOT NULL,
    )
GO


CREATE TABLE dbo.FOODDRIVES
    (
        ID INT IDENTITY(1,1),
        ORG_ID INT NOT NULL,
        START_DATE DATETIME NOT NULL,
        END_DATE DATETIME NOT NULL,
        ACTIVE BIT NOT NULL,
        GOAL FLOAT,
        LOGO_FILE VARCHAR(255),
        PAGE_CONFIG_ID INT,
        FOODDRIVE_CAT_ID INT,
        IS_DELETED BIT
    )
GO


CREATE TABLE dbo.ORGANIZATIONS
    (
        ID INT IDENTITY(1,1),
        ORG_NAME VARCHAR(255) NOT NULL,
        ADMIN_ID INT NOT NULL,
        ADDRESS_ID INT NOT NULL,
        ORG_CAT_ID INT,
        LOGO_FILE IMAGE
    )
GO


CREATE TABLE dbo.DONATIONS
    (
        ID INT IDENTITY(1,1),
        USER_ID INT NOT NULL,
        AMOUNT FLOAT NOT NULL,
        DONATION_DATE DATETIME NOT NULL,
        FOODDRIVE_ID INT NOT NULL,
        PAYMENT_CONFIRM_CODE VARCHAR(255),
        PAYMENT_CONFIRMED BIT,
        TRANSACTION_ID INT
    )
GO


CREATE TABLE dbo.PAGE_CONFIG
    (
        ID INT IDENTITY(1,1),
        FOODDRIVE_ID INT NOT NULL,
        BG_IMAGE_ID INT
    )
GO


CREATE TABLE dbo.APP_CONFIG
    (
        ID INT IDENTITY(1,1)
    )
GO


CREATE TABLE dbo.USER_CAT
    (
        ID INT IDENTITY(1,1),
        TYPE_LABEL VARCHAR(255) NOT NULL
    )
GO


CREATE TABLE dbo.ORG_CAT
    (
        ID INT IDENTITY(1,1),
        TYPE_LABEL VARCHAR(255) NOT NULL
    )
GO


CREATE TABLE dbo.MATCHING_CAMPAIGNS
    (
        ID INT IDENTITY(1,1),
        OWNER_ID INT NOT NULL,
        OWNER_TYPE_ID INT NOT NULL,
        START_DATE DATETIME NOT NULL,
        END_DATE DATETIME NOT NULL,
        ACTIVE BIT NOT NULL,
        PER_MATCH_MINIMUM FLOAT NOT NULL,
        PER_MATCH_MAXIMUM FLOAT NOT NULL,
        MATCH_DONOR_COUNT_LIMIT INT NOT NULL,
        FOODDRIVE_ID INT NOT NULL
    )
GO


